BEGIN TRAN

-- DROP EXISTING TABLES

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'Meaning' AND type = 'U' )
	DROP TABLE [Meaning]

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'Word' AND type = 'U' )
	DROP TABLE [Word]

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'User' AND type = 'U' )
	DROP TABLE [User]

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'Unit' AND type = 'U' )
	DROP TABLE Unit

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'Language' AND type = 'U' )
	DROP TABLE [Language]

-- CREATE TABLES
CREATE TABLE dbo.Unit
(
	[Id] bigint PRIMARY KEY NOT NULL IDENTITY(1,1),
	[Name] varchar(max) NOT NULL
)

CREATE TABLE dbo.[Language]
(
	[Id] bigint PRIMARY KEY NOT NULL IDENTITY(1,1),
	[Name] varchar(max) NOT NULL,
	[TwoLetterISOName] varchar(max) NOT NULL	
)

CREATE TABLE dbo.[User]
(
	[Id] bigint PRIMARY KEY NOT NULL IDENTITY(1,1),
	[Login] varchar(50) NOT NULL,
	[Password] varchar(50) NOT NULL,
	[Name] varchar(50) NOT NULL,
	[NativeLanguage] bigint NOT NULL CONSTRAINT [FK_User_NativeLanguage] FOREIGN KEY REFERENCES [Language] ([Id]),
	[LanguageToLearn] bigint NOT NULL CONSTRAINT [FK_User_LanguageToLearn] FOREIGN KEY REFERENCES [Language] ([Id]),
)

CREATE TABLE dbo.[Word]
(
	[Id] bigint PRIMARY KEY NOT NULL IDENTITY(1,1),
	[OriginalWord] varchar(max) NOT NULL,
	[Definition] varchar(max) NOT NULL,
	[SampleSentence] varchar(max) NULL,
	[Pronunciation] varchar(max) NULL,
	[RecordedAt] datetime NOT NULL,
	[ModifiedAt] datetime NOT NULL,
	[GrammaticalCategory] varchar(max) NOT NULL,

	[Language] bigint NOT NULL CONSTRAINT [FK_Word_Language] FOREIGN KEY REFERENCES [Language] ([Id]),
	[CreatedBy] bigint NOT NULL CONSTRAINT [FK_Word_CreatedBy] FOREIGN KEY REFERENCES [User] ([Id]),
	[ModifiedBy] bigint NOT NULL CONSTRAINT [FK_Word_RecordedBy] FOREIGN KEY REFERENCES [User] ([Id]),
	[Unit] bigint NULL CONSTRAINT [FK_Word_Unit] FOREIGN KEY REFERENCES [Unit] ([Id])
)

CREATE TABLE dbo.[Meaning]
(
	[Id] bigint PRIMARY KEY NOT NULL IDENTITY(1,1),
	[MeaningInSpecifiedLanguage] varchar(max) NOT NULL,
	[Language] bigint NOT NULL CONSTRAINT [FK_Meaning_Language] FOREIGN KEY REFERENCES [Language] ([Id]),
	[Word] bigint NOT NULL CONSTRAINT [FK_Meaning_Word] FOREIGN KEY REFERENCES [Word] ([Id])
)

COMMIT